clear
set more off

use "${wd}/nlrb/nlrb7710_originalfarber.dta",clear

g year=year(closing_date)

// Geography crosswalk firstly
drop if unit_loc_state=="PR"
drop if unit_loc_state=="GU"
drop if unit_loc_state=="VI"
drop if unit_loc_state==""

g casetype=substr(nlrb_id,4,2)

g state=.
g state2=.
do "${nlrb}/statefix.do"
do "${nlrb}/statefix2.do"

tab year
tab year if unit_location_county==. 
drop if unit_location_county==.


tostring state,g(statestr)
tostring unit_location_county,g(strcnty)
replace strcnty="0"+strcnty if length(strcnty)==2 
replace strcnty="00"+strcnty if length(strcnty)==1 

g county=statestr+strcnty
destring county,replace

// Create city defs
g city=.
preserve
	keep if year<=1980
	do "${nlrb}/county_city1973_1980"
	save "${temp}/part1.dta",replace
restore
preserve
	keep if year>1980 & year<=1989
	do "${nlrb}/county_city1981_1989"
	save "${temp}/part2.dta",replace
restore
preserve
	keep if year>1989 & year<=1992
	do "${nlrb}/county_city1990_1992"
	save "${temp}/part3.dta",replace
restore
preserve
	keep if year>1992 & year<=2003
	do "${nlrb}/county_city1993_plus"
	save "${temp}/part4.dta",replace
restore
preserve
	keep if year>2003
	do "${nlrb}/county_city2004_plus"
	save "${temp}/part5.dta",replace
restore

clear
append using "${temp}/part1.dta" "${temp}/part2" "${temp}/part3" "${temp}/part4" ///
	"${temp}/part5"

// Now create proper city definitions here which are consistent across data sets	
	
do "${nlrb}/labels_city_final"	
do "${nlrb}/labels_state"	
	
g city_plus=city
g city_plus2=city

replace city_plus = 17 if city_plus == 44
replace city_plus2 = 17 if city_plus2 == 44

// City Defs
replace city_plus=	58	if state2==	12	& city==.
replace city_plus=	59	if state2==	14	& city==.
replace city_plus=	60	if state2==	21	& city==.
replace city_plus=	61	if state2==	22	& city==.
replace city_plus=	62	if state2==	23	& city==.
replace city_plus=	63	if state2==	31  & city==.
replace city_plus=	64	if state2==	32	& city==.
replace city_plus=	65	if state2==	33	& city==.
replace city_plus=	66	if state2==	34	& city==.
replace city_plus=	67	if state2==	35	& city==.
replace city_plus=	68	if state2==	41	& city==.
replace city_plus=	69	if state2==	43	& city==.
replace city_plus=	70	if state2==	47	& city==.
replace city_plus=	71	if state2==	52	& city==.
replace city_plus=	72	if state2==	53	& city==.
replace city_plus=	73	if state2==	54	& city==.
replace city_plus=	74	if state2==	56	& city==.
replace city_plus=	75	if state2==	58	& city==.
replace city_plus=	76	if state2==	59	& city==.
replace city_plus=	77	if state2==	61	& city==.
replace city_plus=	78	if state2==	63	& city==.
replace city_plus=	79	if state2==	72	& city==.
replace city_plus=	80	if state2==	74	& city==.
replace city_plus=	81	if state2==	84	& city==.
replace city_plus=	82	if state2==	91	& city==.
replace city_plus=	83	if state2==	92	& city==.
replace city_plus=	84	if state2==	93	& city==.


replace city_plus2=	58	if state2==	11	& city==.
replace city_plus2=	59	if state2==	12	& city==.
replace city_plus2=	60	if state2==	13	& city==.
replace city_plus2=	61	if state2==	14	& city==.
replace city_plus2=	62	if state2==	15	& city==.
replace city_plus2=	63	if state2==	16	& city==.
replace city_plus2=	64	if state2==	21	& city==.
replace city_plus2=	65	if state2==	22	& city==.
replace city_plus2=	66	if state2==	23	& city==.
replace city_plus2=	67	if state2==	31	& city==.
replace city_plus2=	68	if state2==	32	& city==.
replace city_plus2=	69	if state2==	33	& city==.
replace city_plus2=	70	if state2==	34	& city==.
replace city_plus2=	71	if state2==	35	& city==.
replace city_plus2=	72	if state2==	41	& city==.
replace city_plus2=	73	if state2==	42	& city==.
replace city_plus2=	74	if state2==	43	& city==.
replace city_plus2=	75	if state2==	44	& city==.
replace city_plus2=	76	if state2==	45	& city==.
replace city_plus2=	77	if state2==	46	& city==.
replace city_plus2=	78	if state2==	47	& city==.
replace city_plus2=	79	if state2==	51	& city==.
replace city_plus2=	80	if state2==	52	& city==.
replace city_plus2=	81	if state2==	53	& city==.
replace city_plus2=	82	if state2==	54	& city==.
replace city_plus2=	83	if state2==	55	& city==.
replace city_plus2=	84	if state2==	56	& city==.
replace city_plus2=	85	if state2==	57	& city==.
replace city_plus2=	86	if state2==	58	& city==.
replace city_plus2=	87	if state2==	59	& city==.
replace city_plus2=	88	if state2==	61	& city==.
replace city_plus2=	89	if state2==	62	& city==.
replace city_plus2=	90	if state2==	63	& city==.
replace city_plus2=	91	if state2==	64	& city==.
replace city_plus2=	92	if state2==	71	& city==.
replace city_plus2=	93	if state2==	72	& city==.
replace city_plus2=	94	if state2==	73	& city==.
replace city_plus2=	95	if state2==	74	& city==.
replace city_plus2=	96	if state2==	81	& city==.
replace city_plus2=	97	if state2==	82	& city==.
replace city_plus2=	98	if state2==	83	& city==.
replace city_plus2=	99	if state2==	84	& city==.
replace city_plus2=	100	if state2==	85	& city==.
replace city_plus2=	101	if state2==	86	& city==.
replace city_plus2=	102	if state2==	87	& city==.
replace city_plus2=	103	if state2==	88	& city==.
replace city_plus2=	104	if state2==	91	& city==.
replace city_plus2=	105	if state2==	92	& city==.
replace city_plus2=	106	if state2==	93	& city==.
replace city_plus2=	107	if state2==	94	& city==.
replace city_plus2=	108	if state2== 95	& city==.


drop state
ren state2 state

label val city_plus city_plus
label val city_plus2 city_plus2
label val state state
label val city city


save "${wd}/nlrb/nlrb7710_final_original_farber.dta",replace // 130,195

























